1. What is Amazon (Shopping)?

Amazon is a multinational technology company focusing on e-commerce. From the end customer's perspective, it is a massive online marketplace offering a vast selection of products ranging from books and electronics to groceries and clothing. It provides features like product search, detailed product information, customer reviews, personalized recommendations, various payment options, and expedited shipping (Prime). For sellers, it is a platform to list products and reach a global audience, often utilizing Amazon's fulfillment network (FBA) for storage and shipping.

2. Requirement Analysis

Core Features

• Product Catalog: Searching, browsing by categories, viewing detailed product pages (images, descriptions, variations like size/color).
• User Management: Accounts, addresses, payment methods, Prime membership status.
• Shopping Cart & Checkout: Adding items to cart, calculating taxes/shipping, applying coupons, and processing payments securely.
• Order Management: Order tracking, viewing order history, processing returns and refunds.
• Inventory Management: Tracking stock levels across multiple fulfillment centers and managing seller inventory.
• Reviews & Ratings: Customers leaving text reviews and star ratings for products they've purchased.
• Recommendations: Suggesting products based on browsing history, past purchases, and what similar users bought.

Key Entities & Attributes

To support an Amazon-level scale, the schema must handle immense volume and complexity:

1. User (Customer):

   • user_id (PK)
   • email (Unique)
   • password_hash
   • full_name
   • is_prime (Boolean)
   • created_at

2. Product (Catalog Item):

   • product_id (PK)
   • seller_id (FK to Seller)
   • category_id (FK to Category)
   • title
   • description
   • price
   • average_rating
   • inventory_count (Often managed in a separate inventory service)

3. Product_Variant (For size/color variations):

   • variant_id (PK)
   • product_id (FK)
   • sku
   • attributes (JSON - e.g., {"color": "red", "size": "L"})
   • price_override

4. Cart & Cart_Item:

   • Cart: cart_id (PK), user_id (FK), created_at
   • Cart_Item: cart_item_id (PK), cart_id (FK), product_id (FK), quantity

5. Order:

   • order_id (PK)
   • user_id (FK)
   • total_amount
   • status (Enum: Placed, Processing, Shipped, Delivered, Cancelled)
   • shipping_address_id (FK)
   • payment_id (FK)
   • created_at

6. Order_Item:

   • order_item_id (PK)
   • order_id (FK)
   • product_id (FK)
   • quantity
   • unit_price (Price at time of purchase)

7. Review:

   • review_id (PK)
   • product_id (FK)
   • user_id (FK)
   • rating (1-5)
   • comment (Text)
   • verified_purchase (Boolean)
   • created_at

8. Seller:

   • seller_id (PK)
   • business_name
   • tax_id
   • rating

3. Scale and Performance Considerations

• Search Infrastructure: Relational databases are too slow for complex text searches across billions of products. An external search engine like Elasticsearch is essential. Data must be synchronized from the primary database to the search index.
• Eventual Consistency: In a highly distributed e-commerce system, strong consistency across all services (inventory, order, payment) is often sacrificed for availability and partition tolerance (CAP theorem). For example, the inventory count shown on the product page might be slightly delayed (eventually consistent).
• Microservices Architecture: Amazon operates using a vast array of microservices. The Catalog, Shopping Cart, Checkout, and Recommendation systems are largely decoupled, communicating via asynchronous events (e.g., using Kafka).
• Idempotency: When processing payments or creating orders, operations must be idempotent to prevent duplicate charges or orders if a network request is retried.

4. Extended Entity-Relationship Model

erDiagram
    USER ||--o{ ORDER : "places"
    USER ||--o| CART : "has"
    USER ||--o{ REVIEW : "writes"
    SELLER ||--o{ PRODUCT : "sells"
    CATEGORY ||--o{ PRODUCT : "categorizes"
    PRODUCT ||--o{ PRODUCT_VARIANT : "has variations"
    PRODUCT ||--o{ REVIEW : "receives"
    PRODUCT ||--o{ ORDER_ITEM : "is ordered as"
    CART ||--o{ CART_ITEM : "contains"
    PRODUCT ||--o{ CART_ITEM : "added as"
    ORDER ||--o{ ORDER_ITEM : "includes"

    USER {
        uuid user_id PK
        string email
        boolean is_prime
    }
    SELLER {
        uuid seller_id PK
        string business_name
    }
    PRODUCT {
        uuid product_id PK
        uuid seller_id FK
        uuid category_id FK
        string title
        decimal price
        int inventory_count
    }
    CART_ITEM {
        uuid cart_id FK
        uuid product_id FK
        int quantity
    }
    ORDER {
        uuid order_id PK
        uuid user_id FK
        decimal total_amount
        enum status
    }
    ORDER_ITEM {
        uuid order_id FK
        uuid product_id FK
        int quantity
        decimal unit_price
    }

This content is reserved for Premium Members.